package com.sdlogan.pbudget;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Class that houses the database used by the application. 
 * @author Sean
 *
 */
public class MoneyTable {
	
	  public static final String KEY_NAME = "money_description";
	  public static final String KEY_DESIGNATION = "money_type";
	  public static final String KEY_VALUE = "money_value";
	  public static final String KEY_ROWID = "_id";
	  public static final String KEY_CATEGORY = "category";
	  public static final String KEY_TIME = "occurence";
	  public static final String KEY_MONTH = "month";
	  public static final String KEY_YEAR = "year";
	  public static final String KEY_DAY = "day";
	  
	 
	  private static final String TAG = "MoneyTable";
	  private DataBaseHelper mDbHelper;
	  private SQLiteDatabase mDb;
	 
	  private static final String DATABASE_NAME = "money_database.db";
	  private static final String DATABASE_TABLE = "budget";
	  private static final int DATABASE_VERSION = 1;
	  
	  private final Context ctx;
	
	  private static final String DATABASE_CREATE =
			    "create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
			    + KEY_NAME +" text not null, " + KEY_VALUE + " text not null, " + KEY_DESIGNATION + " text not null, " + KEY_CATEGORY + " text not null, " + KEY_TIME + " text not null, " + KEY_MONTH + " text not null, " + KEY_YEAR + " text not null, " + KEY_DAY + " text not null);";
	
	  /**
	   * Private class that initializes the database or creates it if it does not exist
	   * @author Sean
	   *
	   */
	private static class DataBaseHelper extends SQLiteOpenHelper {

		public DataBaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG,"Creating Database: " + DATABASE_CREATE);
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG,"Upgrading database from version " + oldVersion + " to " + newVersion);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);
		}
		
	};
	
	/**
	 * Constructor
	 * @param c  : Context
	 */
	public MoneyTable(Context c) {
		this.ctx = c;
	}
	
	/**
	 * Opens the database so it can be used
	 * @return : returns the database 
	 * @throws SQLException : throws exception if cannot be opened
	 */
	public MoneyTable open() throws SQLException {
		Log.i(TAG,"Opening Database ..");
		mDbHelper = new DataBaseHelper(ctx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}
	
	/**
	 * Closes the database
	 */
	public void close() {
		Log.i(TAG,"Closing Database");
		mDbHelper.close();
	}
	
	/**
	 * Inserts a row into the database 
	 * @param type : Expense or Income
	 * @param desc : Description
	 * @param value : Value
	 * @param cat  : Category
	 * @param occ  : Occurence
	 * @return     : row id
	 */
	public long createNewRow(String type, String desc, String value, String cat, String occ) {
		Log.i(TAG,"Inserting New Row");
		Calendar cal = Calendar.getInstance();
		String year = Integer.toString(cal.get(Calendar.YEAR),10);
		String day = Integer.toString(cal.get(Calendar.DATE),10);
		Date date = new Date();
		String month = new SimpleDateFormat("MMMM").format(date);
		ContentValues newvalues = new ContentValues();
		newvalues.put(MoneyTable.KEY_DESIGNATION, type);
		newvalues.put(MoneyTable.KEY_NAME, desc);
		newvalues.put(MoneyTable.KEY_VALUE, value);
		newvalues.put(MoneyTable.KEY_CATEGORY, cat);
		newvalues.put(MoneyTable.KEY_TIME, occ);
		newvalues.put(MoneyTable.KEY_MONTH, month);
		newvalues.put(MoneyTable.KEY_YEAR, year);
		newvalues.put(MoneyTable.KEY_DAY, day);
		return mDb.insert(MoneyTable.DATABASE_TABLE, null, newvalues);
	}
	
	/**
	 * Delete the row based on the id
	 * @param rowID : Id of the row to be deleted
	 * @return : if the row was removed
	 */
	public int deleteRowByID(long rowID) {
		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowID, null);
	}
	
	/**
	 * Delete a row based on the description 
	 * @param st : String representation of the description
	 * @return   : Value
	 */
	public int deleteRowByDesc(String st) {
		return mDb.delete(DATABASE_TABLE,KEY_NAME + "=" + st,null);
	}
	
	/**
	 * Get all the values for a specified year
	 * @param year : The year to be selected
	 * @return     : A list of all the values
	 */
	public List<dbPackage> getYearly(String year) {
		List<dbPackage> rows = new ArrayList<dbPackage>();
		Cursor temp = 
				mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_NAME,KEY_VALUE,KEY_DESIGNATION,KEY_CATEGORY,KEY_TIME,KEY_MONTH,KEY_YEAR,KEY_DAY}, "year = ?", new String[]{year}, null, null, null);
				temp.moveToFirst();
				while (!temp.isAfterLast()) {
					dbPackage newP = searchCursor(temp);
					rows.add(newP);
					temp.moveToNext();
				}
				temp.close();
				return rows;
	}
	
	/**
	 * Get all the values for a specific month
	 * @param month : The month to be used
	 * @return      : The list of all the values
	 */
	public List<dbPackage> getMonthly(String month) {
		List<dbPackage> rows = new ArrayList<dbPackage>();
		Cursor temp = 
				mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_NAME,KEY_VALUE,KEY_DESIGNATION,KEY_CATEGORY,KEY_TIME,KEY_MONTH,KEY_YEAR,KEY_DAY}, "month = ?", new String[]{month}, null, null, null);
				temp.moveToFirst();
				while (!temp.isAfterLast()) {
					dbPackage newP = searchCursor(temp);
					rows.add(newP);
					temp.moveToNext();
				}
				temp.close();
				return rows;
	}
	
	/**
	 * Get all the rows in the database
	 * @return : All the data
	 */
	public List<dbPackage> getAllRows() {
		List<dbPackage> allRows = new ArrayList<dbPackage>();
		Cursor temp = 
		mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_NAME,KEY_VALUE,KEY_DESIGNATION,KEY_CATEGORY,KEY_TIME,KEY_MONTH,KEY_YEAR,KEY_DAY}, null, null, null, null, null);
		temp.moveToFirst();
		while (!temp.isAfterLast()) {
			dbPackage newP = searchCursor(temp);
			allRows.add(newP);
			temp.moveToNext();
		}
		temp.close();
		return allRows;
	}
	
	/**
	 * Class used to filter the results from the database and return them in a list of db packages
	 * @param c : The cursor to be used
	 * @return  : A db package 
	 */
	public dbPackage searchCursor(Cursor c){
		dbPackage temp = new dbPackage();
		temp.setID(c.getLong(0));
		temp.setDescrip(c.getString(1));
		temp.setType(c.getString(3));
		temp.setValue(c.getString(2));
		temp.setCategory(c.getString(4));
		temp.setOccurence(c.getString(5));
		temp.setMonth(c.getString(6));
		temp.setYear(c.getString(7));
		temp.setDay(c.getString(8));
		temp.setYearlyValue(c.getString(2));
		temp.setMonthlyValue(c.getString(2));
		return temp;
	}
	
	/**
	 * Return only expenses
	 * @return : The list of all the expenses
	 * @throws SQLException
	 */
	public List<dbPackage> getExpenses() throws SQLException {
		List<dbPackage> allRows = new ArrayList<dbPackage>();
		Cursor temp = 
		mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_NAME,KEY_VALUE,KEY_DESIGNATION,KEY_CATEGORY,KEY_TIME,KEY_MONTH,KEY_YEAR,KEY_DAY}, "money_type = ?", new String[]{"Expense"}, null, null, null);
		temp.moveToFirst();
		while (!temp.isAfterLast()) {
			dbPackage newP = searchCursor(temp);
			allRows.add(newP);
			temp.moveToNext();
		}
		temp.close();
		return allRows;
	}
	/**
	 * Return only incomes
	 * @return : List of all incomes
	 * @throws SQLException
	 */
	public List<dbPackage> getIncomes() throws SQLException {
		List<dbPackage> allRows = new ArrayList<dbPackage>();
		Cursor temp = 
		mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_NAME,KEY_VALUE,KEY_DESIGNATION,KEY_CATEGORY,KEY_TIME,KEY_MONTH,KEY_YEAR,KEY_DAY}, KEY_DESIGNATION + " = ?", new String[] {"Income"}, null, null, null);
		temp.moveToFirst();
		while (!temp.isAfterLast()) {
			dbPackage newP = searchCursor(temp);
			allRows.add(newP);
			temp.moveToNext();
		}
		temp.close();
		return allRows;
	}
	
}
